Skip to main content

Settings and Keywords

Keyword WITH

Use WITH keyword to declare aliases, variables, expressions. You can use the declared elements in the rest of the query.

Examples with a keyword WITH
WITH
entries AS array(deltix.timebase.api.messages.universal.L2EntryNew) AS 'l2',
max(l2[side == BID].price) AS 'maxBid',
min(l2[side == ASK].price) AS 'minAsk'
SELECT
max{}(maxBid) AS 'highBid',
min{}(minAsk) AS 'lowAsk'
FROM kraken
OVER time(1m)
WHERE packageType == PERIODICAL_SNAPSHOT AND symbol == 'BTCUSD'

Keyword TYPE

Use keyword TYPE to set the type name of the query result. Can be used to map the query output to the specific class type.

Examples with a keyword TYPE
SELECT
bbo[side == ASK].price AS "offerPrice",
bbo[side == ASK].size AS "offerSize",
bbo[side == BID].price AS "bidPrice",
bbo[side == BID].size AS "bidSize"
TYPE "deltix.timebase.api.messages.BestBidOfferMessage"
FROM kraken
ARRAY JOIN (entries AS array(deltix.timebase.api.messages.universal.L1entry))[THIS IS NOT NULL] AS bbo

Keyword FIELD

You can use the FIELD keyword to assign names to columns in the SELECT expression.

tip

It is important to differentiate column names that can be set using FIELD keyword from aliases set by AS keyword. You cannot have more than one alias with the same name, but with FIELD, you can assign the same names to more than one column - refer to the below example.

Refer to Alias to learn more.

SELECT
RECORD entry.price FIELD 'price' TYPE t1 WHEN entry is L1Entry
RECORD entry.price FIELD 'price', entry.size FIELD 'size' TYPE t2 WHEN entry is L2EntryNew
FROM binance
array join entries AS entry

Keyword THIS

Use keyword THIS to reference the current message as object.

Examples with a keyword THIS
#returns the entire message as object as a single field
SELECT THIS FROM packages

#returns entries array from the current message
SELECT THIS.entries FROM packages

Case and Special Characters

Identifiers in QQL are not case sensitive and get converted to upper case in case not embraced in double quotes. The following rule applies:

  • test = Test = TEST
  • "test" != "Test" != "TEST"

You do not have to embrace identifiers in quotes, but mind the following use cases:

  • use double quotes with identifier's name in case it includes special characters
  • use double quotes with identifier's name in case it starts with a numerical

QQL processor performs case-insensitive matching of fields, classes, streams.

QQL supports backslash quotation, such as \', \", \\, \n, \r, \t, \b, \f.

Example: s4'2 in QQL query write as 's4\'2'

Keywords LIMIT and OFFSET

LIMIT keyword sets the number of records returned by the query. For example, adding limit 10 to the query will return just 10 records. OFFSET keyword applies to LIMIT and sets the starting record number. For example, offset 100 added to the query will return all records starting from the 101th record (when counting from 1).

Follow these rules to add LIMIT and OFFSET to QQL queries:

  1. limit <limit count>
  2. limit <limit count> offset <offset count>
  3. limit <offset count>, <limit count>

As a use case example, a combination of LIMIT and OFFSET can be applied to creating pagination.

Examples of LIMIT and OFFSET keywords
SELECT RUNNING entries, count{}() FROM kraken
LIMIT 10 OFFSET 5

SELECT RUNNING entries, count{}() FROM kraken
LIMIT 10, 15
tip

Please note, that a query with the OFFSET will still read all the records on the server side and return just the records considering the specified offset.